package service

import (
	"seat-service/initialization"
	"seat-service/model/dto"
	"time"
)

type DataViewService struct {
}

const (
	tableName    = "reserves"
	tableStudent = "students"
)

func (d *DataViewService) QueryReserveInformation() (reserveInformation dto.ReserveData, err error) {
	//天
	var dayReserveCount int64     //当天预约次数
	var yestDayReserveCount int64 //昨天的预约次数
	today := time.Now().Truncate(24 * time.Hour)
	tommorow := today.Add(24 * time.Hour)
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", today, tommorow).Count(&dayReserveCount).Error //今天的预约次数
	if err != nil {
		return
	}
	yesterday := time.Now().Truncate(24 * time.Hour).Add(-24 * time.Hour)
	today = yesterday.Add(24 * time.Hour)
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", yesterday, today).Count(&yestDayReserveCount).Error //昨天的预约次数
	if err != nil {
		return
	}
	reserveInformation.DayReserveCount = dayReserveCount
	reserveInformation.LastDayReserveCount = yestDayReserveCount
	//周
	var weekReserveCount int64     //本周预约次数
	var lastWeekReserveCount int64 //上周预约次数
	weekStart := today.AddDate(0, 0, -int(today.Weekday()))
	weekEnd := weekStart.AddDate(0, 0, 7)
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", weekStart, weekEnd).Count(&weekReserveCount).Error
	if err != nil {
		return
	}
	lastWeekStart := today.AddDate(0, 0, -int(today.Weekday())-7)
	lastWeekEnd := lastWeekStart.AddDate(0, 0, 7)
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", lastWeekStart, lastWeekEnd).Count(&lastWeekReserveCount).Error
	if err != nil {
		return
	}
	reserveInformation.WeekReserveCount = weekReserveCount
	reserveInformation.LastWeekReserveCount = lastWeekReserveCount
	//月
	var monthReserveCount int64     //本月预约次数
	var lastMonthReserveCount int64 //上月的预约次数
	monthStart := time.Date(today.Year(), today.Month(), 1, 0, 0, 0, 0, today.Location())
	nextMonth := monthStart.AddDate(0, 1, 0)
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", monthStart, nextMonth).Count(&monthReserveCount).Error
	if err != nil {
		return
	}
	lastMonthStart := time.Date(today.Year(), today.Month()-1, 1, 0, 0, 0, 0, today.Location())
	thisMonthStart := time.Date(today.Year(), today.Month(), 1, 0, 0, 0, 0, today.Location())
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", lastMonthStart, thisMonthStart).Count(&lastMonthReserveCount).Error
	if err != nil {
		return
	}
	reserveInformation.MonthReserveCount = monthReserveCount
	reserveInformation.LastMonthReserveCount = lastMonthReserveCount
	//年
	var yearReserveCount int64 //本年预约次数
	var lastReserveCount int64 //上一年预约次数
	yearStart := time.Date(today.Year(), 1, 1, 0, 0, 0, 0, today.Location())
	nextYear := yearStart.AddDate(1, 0, 0)
	err = initialization.DB.Table(tableName).Where("created_at >= ? and created_at < ?", yearStart, nextYear).Count(&yearReserveCount).Error
	if err != nil {
		return
	}
	lastYearStart := time.Date(today.Year()-1, 1, 1, 0, 0, 0, 0, today.Location())
	thisYearStart := time.Date(today.Year(), 1, 1, 0, 0, 0, 0, today.Location())
	err = initialization.DB.Table(tableName).Where("created_at = ? and created_at < ?", lastYearStart, thisYearStart).Count(&lastReserveCount).Error
	if err != nil {
		return
	}
	reserveInformation.YearReserveCount = yearReserveCount
	reserveInformation.LastYearReserveCount = lastReserveCount
	return
}

func (d *DataViewService) QueryReserveDataDay(dayStart time.Time, dayEnd time.Time) (reserveDayCount []dto.ReserveDayCount, err error) {
	//将日期转化为所需格式
	formattedDayStart := dayStart.Format("2006-01-02")
	formattedDayEnd := dayEnd.Format("2006-01-02")
	//按天分组查询 DATE提取日期部分
	err = initialization.DB.Table(tableName).Select("DATE(created_at) as day_time , count(id) as day_reserve_counts").
		Where("DATE(created_at) >= ? and DATE(created_at) <= ?", formattedDayStart, formattedDayEnd).
		Group("day_time").Order("day_time").
		Scan(&reserveDayCount).Error
	return
}

func (d *DataViewService) QueryReserveDataMonth(monthStart time.Time, monthEnd time.Time) (reserveMonthCount []dto.ReserveMonthCount, err error) {
	//按月分组查询
	err = initialization.DB.Table(tableName).Select("DATE_FORMAT(created_at, '%Y-%m') as month_time , count(id) as month_reserve_counts").
		Where("created_at BETWEEN ? AND ?", monthStart, monthEnd).Group("month_time").Order("month_time").Scan(&reserveMonthCount).
		Error
	return
}

func (d *DataViewService) QueryReserveInformationByCollege(timeType uint) (collegeReserveInformation []dto.ReserveInformationCollege, err error) {
	nowTime := time.Now()
	var floatTime string
	if timeType == 0 {
		//近一周
		floatTime = nowTime.AddDate(0, 0, -7).Format("2006-01-02")
	} else if timeType == 1 {
		//近半个月
		floatTime = nowTime.AddDate(0, 0, -15).Format("2006-01-02")
	} else {
		//近一个月
		floatTime = nowTime.AddDate(0, -1, 0).Format("2006-01-02")
	}
	err = initialization.DB.Table(tableStudent).
		Select("students.college as college_name , count(reserves.id) as reserve_person_count").
		Joins("LEFT JOIN reserves ON students.id = reserves.student_id").
		Where("DATE(reserves.created_at) >= ? AND DATE(reserves.created_at) <= ?", floatTime, nowTime.Format("2006-01-02")).
		Group("college_name").
		Scan(&collegeReserveInformation).Error
	return
}

func (d *DataViewService) QueryReserveInformationByRoom() (classroomReserveInformation []dto.ReserveInformationClassroom, err error) {
	//获取当前时间
	now := time.Now()
	//计算一个月前的日期
	oneMonthAgo := now.AddDate(0, -1, 0)
	//预约表中有教室id , 根据教室id去教室表中查询到教室号以及教室所属的楼栋id , 根据楼栋id在楼栋表中查询出楼栋名字 , 在预约表中统计每一个教室被预约的次数 进一个月
	err = initialization.DB.Table(tableName).Where("reserves.created_at >= ? and reserves.created_at <= ?", oneMonthAgo, now).Select("classrooms.location as location,buildings.name as build_name , count(reserves.id) as reserve_count").
		Joins("LEFT JOIN classrooms ON reserves.classroom_id = classrooms.id").
		Joins("LEFT JOIN buildings ON classrooms.building_id = buildings.id").
		Group("classrooms.location,buildings.name").
		Order("reserve_count desc").
		Limit(7).
		Scan(&classroomReserveInformation).Error
	return
}
